﻿create function [String].[Split] ( @p_String varchar(max), @p_Delimiter char(1) = ',' )
returns @ReturnTable table (
  [Index] int identity(1, 1) not null,
  [StringPart] varchar(4000) not null
) as
begin

   declare @EndPosition int
   declare @StartPosition int = 0;
   declare @TextPosition int = 1;
   declare @ChunkLength smallint
   declare @TemporaryString varchar(4000) = '';
   declare @LeftOverString varchar(4000) = '';
   declare @TemporaryValue varchar(4000) = '';

   while @TextPosition <= Len( @p_String )
   begin
      set @ChunkLength = 4000 - Len( @LeftOverString )
      set @TemporaryString = @LeftOverString + SubString( @p_String, @TextPosition, @ChunkLength )
      set @TextPosition = @TextPosition + @ChunkLength
      set @EndPosition = CharIndex( @p_Delimiter, @TemporaryString )
      while @EndPosition > 0
      begin
         insert @ReturnTable values ( LTrim( RTrim( SubString( @TemporaryString, @StartPosition + 1, @EndPosition - @StartPosition - 1 ) ) ) )
         set @StartPosition = @EndPosition
         set @EndPosition = CharIndex( @p_Delimiter, @TemporaryString, @StartPosition + 1 )
      end
      set @LeftOverString = Right( @TemporaryString, Len( @TemporaryString ) - @StartPosition )
   end

   insert @ReturnTable values ( LTrim( RTrim( @LeftOverString ) ) )

   return

end